﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace DAL
{
    /**
     * 
     * DataProvider lié aux Commandes.
     * 
     **/
    public class OrderDataProvider : BaseDataProvider
    {
        private string table = "order_view"; // nom de la table
        private string id = "id_order"; // nom de la clé primaire

        public override string getTable()
        {
            return this.table;
        }

        public override string getId()
        {
            return this.id;
        }

        /**
        * 
        * Renvoie la table commande avec les clients
        * 
        **/

        public DataTable findAllOrder()
        {
            DataTable dt = new DataTable();

            using (MySqlConnection conn = new MySqlConnection(this.connString))
            {
                conn.Open();
                MySqlDataAdapter adpt = new MySqlDataAdapter();
                adpt.SelectCommand = new MySqlCommand("SELECT id_order,created_at,customer.name,customer.firstname FROM " + getTable() + " inner join customer on id_customer = customer_id_customer", conn);
                adpt.Fill(dt);
            }

            return dt;
        }

        /**
        * 
        * Supprime un record selon son id
        * 
        **/
        public bool deleteCommande(int id)
        {
            using (MySqlConnection conn = new MySqlConnection(this.connString))
            {
                conn.Open();

                MySqlCommand cmd = new MySqlCommand("DELETE FROM order_detail WHERE order_id_order = @id", conn);
                cmd.Parameters.AddWithValue("@id", id);

                cmd.ExecuteNonQuery();

                MySqlCommand cmd2 = new MySqlCommand("DELETE FROM " + getTable() + " WHERE " + getId() + " = @id", conn);
                cmd2.Parameters.AddWithValue("@id", id);

                cmd2.ExecuteNonQuery();


            }
            return true;
        }

        /**
        * 
        * Renvoie 1 record d'une table selon l'id passé en paramètre
        * 
        **/
        virtual public DataRow findOneCommande(int id)
        {
            DataTable dataDT = new DataTable();

            using (MySqlConnection conn = new MySqlConnection(this.connString))
            {
                conn.Open();
                MySqlDataAdapter adpt = new MySqlDataAdapter();
                adpt.SelectCommand = new MySqlCommand("SELECT * FROM " + getTable() + " inner join customer on order_view.customer_id_customer=customer.id_customer WHERE order_view.id_order = @id", conn);
                adpt.SelectCommand.Parameters.AddWithValue("@id", id);
                adpt.Fill(dataDT);
            }

            if (dataDT.Rows.Count > 0)
                return dataDT.Rows[0];
            else
                return null;
        }

        /**
        * 
        * Renvoie les produits d'une commande 
        * 
        **/

        public DataTable findAllproductOrder(int id)
        {
            DataTable dt = new DataTable();

            using (MySqlConnection conn = new MySqlConnection(this.connString))
            {
                conn.Open();
                MySqlDataAdapter adpt = new MySqlDataAdapter();
                adpt.SelectCommand = new MySqlCommand("SELECT * FROM order_detail inner join product on order_detail.product_id_product=product.id_product WHERE order_detail.order_id_order = @id", conn);
                adpt.SelectCommand.Parameters.AddWithValue("@id", id);
                adpt.Fill(dt);
            }

            return dt;
        }
    }
}
